<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@page import="java.util.*" %>
<%@page import="java.text.SimpleDateFormat" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Bank Ledger Report</title>
</head>
<body>

<form name="bankledgerreport" method="get" action="BankLedgerPeriodicServlet">

   <%
        String exportToExcel = request.getParameter("exportToExcel");
        if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "BankLedgerReport.xls");
 
        }
    %>


<%
			String selectedbname = (String) request.getAttribute("bankname");
%>

<h2 align="center"><%=selectedbname %>Bank Ledger Report</h2>

<table align="center" border=5 width=500>
<%  DBConnection dbc=new DBConnection();
			Connection con=dbc.getNewConnection();
			Statement st = null;
			ResultSet rs = null;
			
			int count=1;
			double debtors=0, creditors=0, cashbank=0, fixedassets=0, other=0, capital=0, otherexpenses=0, sellingandmarketing=0, salaryandwages=0, administrativecharges=0, provision=0, unsecuredloans=0, securedloans=0, investments=0, closingstock=0, openingstock=0, loansandadvances=0, indirectincome=0, bankchargesandinterest=0, depreciation=0; //trial balance
			
			double voucher=0.0,v3=0.0;
			
			String v5="",b5="",d="",d1="",d2="",d3="",min="",max="";
			%>
			<%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MIN(DATE) FROM ( (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) ) p");
			
			while(rs.next()){
			
						 			
						        min=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}//code	        
						     %>
						     
						     
						     <%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MAX(DATE) FROM ( (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) ) p");
			
			while(rs.next()){
			
						 			
						        max=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}//code	        
						     %>
						     
						     <tr>
						    <td colspan=5> From:<%=min %> <br>  
						     To:<%=max %>
						     </tr>

<tr>
		<td colspan=5 align=center> BOA  </td>
</tr>
 
 
 
<tr>
		<th> Date </th>
		<th> Particulars </th>
		<th> Voucher type </th>
 		<th> Voucher Number </th>
 		<th> Amount </th>
</tr>

<% 
		double total=0.0;
		double ar14=0.0, ar24=0.0, ap14=0.0, ap24=0.0;  %>
		
		
		<input type="hidden" name="bname" value="<%=selectedbname%>"/>
		
		<%
   		
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,raccname,ramount FROM ReceiptsVoucher2 where bname='"+selectedbname+"';");
		   
		   while(rs.next())
		   {
		        String r11=rs.getString(1); //receipts voucher number	        
		        String rdate12=rs.getString(2); //date
		        String r13=rs.getString(3); //account name
				double r14=rs.getDouble(4); //amount22
				
				ar14=ar14+r14;
%>
<tr>
			
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String r12 =formatter.format(fromsql.parse(rdate12));
            		%>
            		
			<td> <%=r12 %> </td>
			<td> <%=r13 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r11%>&group=Receipts"> <%=r11 %> </a></td>
			<td> <%=r14 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>

<%--
<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,raccname2,ramount2 FROM ReceiptsVoucher2 where bname='"+selectedbname+"';");
		   
		   while(rs.next())
		   {
		        String r21=rs.getString(1); //receipts voucher number	        
		        String rdate22=rs.getString(2); //date
		        String r23=rs.getString(3); //account name
				double r24=rs.getDouble(4); //amount22
				
				ar24=ar24+r24;
%>
<tr>

			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String r22 =formatter.format(fromsql.parse(rdate22));
            		%>
			<td> <%=r22 %> </td>
			<td> <%=r23 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r21%>&group=Payment"> <%=r21 %> </a></td>
			<td> <%=r24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>
 --%>
 
<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,pyaccname,-(pyamount) FROM PaymentVoucher2 where bname='"+selectedbname+"';");
		   
		   while(rs.next())
		   {
		        String p11=rs.getString(1); //Payment voucher number	        
		        String pdate12=rs.getString(2); //date
		        String p13=rs.getString(3); //account name
				double p14=rs.getDouble(4); //amount22
				
				ap14=ap14+p14;
%>
<tr>

			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String p12 =formatter.format(fromsql.parse(pdate12));
            		%>
			<td> <%=p12 %> </td>
			<td> <%=p13 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p11%>&group=Payment"> <%=p11 %> </a></td>
			<td> <%=p14 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%--
<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,pyaccname2,-(pyamount2) FROM PaymentVoucher2 where bname='"+selectedbname+"';");
		   
		   while(rs.next())
		   {
		        String p21=rs.getString(1); //Payment voucher number	        
		        String pdate22=rs.getString(2); //date
		        String p23=rs.getString(3); //account name
				double p24=rs.getDouble(4); //amount22
				
				ap24=ap24+p24;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String p22 =formatter.format(fromsql.parse(pdate22));
            		%>
			<td> <%=p22 %> </td>
			<td> <%=p23 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p21%>&group=Payment"> <%=p21 %></a> </td>
			<td> <%=p24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>
 --%>
 
	<% total=ar14+ap14; %>
<tr>
		<td> </td>
		<td> Total </td>
		<td> </td>
		<td> </td>
		<td> <%=total %></td>
</tr>

</table>

<br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <%
        if (exportToExcel == null) {
    %>
    <a href="BankLedgerReport.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>
<br><br>
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
From <input type=text name=fromdate > To <input type=text name=todate >
		 <input type=submit name="datewise" value="Date Wise">

</form>
</body>
</html>